package com.ai.sml.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 下载EXCEL文件
 * 
 * @author ShenHuaJie
 * @since 2011-11-08
 */
public class DownloadExcelUtil {
	private static final Log log = LogFactory.getLog(DownloadExcelUtil.class);

	private OutputStream os; // 获得输出流
	private Workbook wbook; // 创建excel文件
	private Sheet wsheet; // 工作表

	private Integer titleCols = 0;
	private long startTime;
	private int sheetIndex = 1;
	private String sheetName;
	private String[] titles;
	private Font normalFont;
	private Font boldFont;

	/**
	 * 设置文件名和工作表名(Excel)
	 * 
	 * @param response
	 *            为NULL时，写入磁盘
	 * @param fileName
	 *            文件名
	 * @param sheetName
	 *            工作表名
	 * @throws IOException
	 */
	public DownloadExcelUtil(OutputStream outputStream, String sheetName) {
		startTime = System.currentTimeMillis();
		os = outputStream;
		wbook = new HSSFWorkbook(); // 创建excel文件
		wsheet = wbook.createSheet(sheetName + "_" + sheetIndex); // sheet名称

		normalFont = wbook.createFont();
		normalFont.setFontName("宋体");
		normalFont.setFontHeightInPoints((short) 10);

		boldFont = wbook.createFont();
		boldFont.setFontName("宋体");
		boldFont.setFontHeightInPoints((short) 12);
		boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	}

	public void addSheet(String sheetName) {
		sheetIndex++;
		this.sheetName = sheetName;
		wsheet = wbook.createSheet(sheetName + "_" + sheetIndex);
	}

	public void setTitles(String[] titles) throws IOException {
		this.titles = titles;
		addRow(titles);
	}

	/**
	 * 添加一行
	 * 
	 * @param strings
	 *            该行数据
	 * @throws IOException
	 * @throws WriteException
	 */
	public void addRow(Object[] strings) throws IOException {
		Row row = wsheet.createRow(wsheet.getLastRowNum());
		row.setHeight((short) 300);

		CellStyle headStyle = wbook.createCellStyle();
		headStyle.setFont(boldFont);
		headStyle.setWrapText(true);
		headStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
		headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		for (int i = 0; i < strings.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(strings[i] == null ? "" : strings[i].toString());
			wsheet.setColumnWidth(i, (int) Math.round(cell.getStringCellValue().length() * 3.0) * 256);
		}
		os.flush();
	}

	/**
	 * 添加多行
	 * 
	 * @param infoList
	 *            报表内容
	 * @param cellTypes
	 *            单元格样式
	 * @throws IOException
	 */
	public void addRows(int start, List<Map<String, String>> infoList, String[] keys, int[] cellTypes)
			throws IOException {
		if (infoList != null && !infoList.isEmpty()) {
			// 内容
			CellStyle cellStyle = getCellStyle(Cell.CELL_TYPE_STRING);
			int startRow = start;
			for (; !infoList.isEmpty();) {
				if (startRow == 60000) {
					this.setColumnView();
					this.addSheet(sheetName);
					setTitles(titles);
				}
				startRow++;
				Map<String, String> rowInfo = infoList.get(0);
				if (keys.length > titleCols) {
					titleCols = keys.length;
				}
				Row row = wsheet.createRow(startRow);
				row.setHeight((short) 300);
				for (int j = 0; j < keys.length; j++) {
					Cell cell = row.createCell(j);
					String value = rowInfo.get(keys[j]);
					cell.setCellValue(value == null ? "" : value);
					if (cellTypes != null) {
						cell.setCellType(cellTypes[j]);
					}
					cell.setCellStyle(cellStyle);
				}
				infoList.remove(0);
			}
		}
	}

	/**
	 * 添加多行
	 * 
	 * @param infoList
	 *            报表内容
	 * @param cellTypes
	 *            单元格样式
	 * @throws IOException
	 */
	public void addRows(List<Map<String, String>> infoList, String[] keys, int[] cellTypes) throws IOException {
		if (infoList != null && !infoList.isEmpty()) {
			// 内容
			CellStyle cellStyle = getCellStyle(Cell.CELL_TYPE_STRING);
			for (; !infoList.isEmpty();) {
				if (wsheet.getLastRowNum() == 60000) {
					this.setColumnView();
					this.addSheet(sheetName);
					setTitles(titles);
				}
				Map<String, String> rowInfo = infoList.get(0);
				if (keys.length > titleCols) {
					titleCols = keys.length;
				}
				Row row = wsheet.createRow(wsheet.getLastRowNum() + 1);
				row.setHeight((short) 300);
				for (int j = 0; j < keys.length; j++) {
					Cell cell = row.createCell(j);
					String value = rowInfo.get(keys[j]);
					cell.setCellValue(value == null ? "" : value);
					if (cellTypes != null) {
						cell.setCellType(cellTypes[j]);
					}
					cell.setCellStyle(cellStyle);
				}
				infoList.remove(0);
			}
		}
	}

	/**
	 * 添加多行
	 * 
	 * @param infoList
	 *            报表内容
	 * @param cellTypes
	 *            单元格样式
	 * @throws IOException
	 */
	public void addRows(List<?> infoList, int[] cellTypes) throws IOException {
		if (infoList != null && !infoList.isEmpty()) {
			// 内容
			CellStyle cellStyle = getCellStyle(Cell.CELL_TYPE_STRING);
			for (; !infoList.isEmpty();) {
				if (wsheet.getLastRowNum() == 60000) {
					this.setColumnView();
					this.addSheet(sheetName);
					setTitles(titles);
				}
				Object[] rowInfo = (Object[]) infoList.get(0);
				if (rowInfo.length > titleCols) {
					titleCols = rowInfo.length;
				}
				Row row = wsheet.createRow(wsheet.getLastRowNum() + 1);
				row.setHeight((short) 300);
				for (int j = 0; j < rowInfo.length; j++) {
					rowInfo[j] = rowInfo[j] == null ? "" : rowInfo[j];
					Cell cell = row.createCell(j);
					cell.setCellValue(rowInfo[j] == null ? "" : rowInfo[j].toString());
					if (cellTypes != null) {
						cell.setCellType(cellTypes[j]);
					}
					cell.setCellStyle(cellStyle);
				}
				infoList.remove(0);
			}
			os.flush();
		}
	}

	/**
	 * 下载Excel
	 * 
	 * @throws IOException
	 * @throws WriteException
	 * @throws Exception
	 */
	public void reportExcel() throws IOException {
		this.setColumnView();
		if (os != null) {
			if (wbook != null) {
				wbook.write(os);
			}
			os.flush();
			os.close();
		}
		log.info("Use time:" + (System.currentTimeMillis() - startTime) / 1000 + "s");
		log.info("ReportExcel Successful!!!");
	}

	/**
	 * 添加单元格
	 * 
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 */
	public CellStyle getCellStyle(int type) {
		CellStyle style = wbook.createCellStyle();
		style.setFont(normalFont);
		style.setWrapText(true);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		// 字体样式
		if (type == Cell.CELL_TYPE_BLANK) {
			style.setFont(boldFont);
			style.setAlignment(CellStyle.ALIGN_CENTER);
		} else if (type == Cell.CELL_TYPE_FORMULA) {
			style.setAlignment(CellStyle.ALIGN_LEFT); // 对齐方式
		} else if (type == Cell.CELL_TYPE_NUMERIC) { // 数字
			style.setAlignment(CellStyle.ALIGN_RIGHT); // 对齐方式
		} else {
			style.setAlignment(CellStyle.ALIGN_CENTER); // 对齐方式
		}
		return style;
	}

	/**
	 * 设置列宽
	 * 
	 * @param cellInfo
	 * @param col
	 */
	public void setColumnView() {
		int infoWidth, cellWidth;
		String value;
		Pattern pattern = Pattern.compile("\\d+(.\\d+)?$");
		Cell cell;
		for (int i = 0; i < wsheet.getLastRowNum(); i++) {
			Row row = wsheet.getRow(i);
			if (row != null) {
				for (int j = 0; j < row.getLastCellNum(); j++) {
					cell = row.getCell(j);
					value = cell.getStringCellValue();
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 日期
						infoWidth = (int) Math.round(value.length() * 0.5);
					} else if (pattern.matcher(value).matches()) { // 数字
						infoWidth = (int) Math.round(value.length() * 1.2);
					} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // 粗体
						infoWidth = (int) Math.round(value.getBytes().length * 1.13);
					} else if (value.getBytes().length != value.length()) {
						infoWidth = (int) Math.round(value.length() * 1.9);
					} else {
						infoWidth = (int) Math.round(value.length() * 1.05);
					}
					cellWidth = wsheet.getColumnWidth(j);
					infoWidth *= 256;
					if (cellWidth < infoWidth) {
						wsheet.setColumnWidth(j, infoWidth);
					}
				}
			}
		}
	}

	/**
	 * 合并单元格
	 * 
	 * @param col
	 *            起始列
	 * @param row
	 *            起始行
	 * @param toCol
	 *            结束列
	 * @param toRow
	 *            结束行
	 * @throws IOException
	 * @throws WriteException
	 * @throws Exception
	 */
	public void setMergeCells(int col, int row, int toCol, int toRow) {
		wsheet.addMergedRegion(new CellRangeAddress(row, toRow, col, toCol));
	}

	public Integer getTitleCols() {
		return titleCols;
	}

	public int getSheetIndex() {
		return sheetIndex;
	}
	/**
	 * 添加多行
	 * @param start
	 * @param infoList
	 * 				报表内容
	 * @param keys
	 * @param cellTypes
	 * 				单元格样式
	 * @param sheetSize
	 * 			每个sheet长度
	 * @throws IOException
	 */
	public synchronized void addRows(int start, List<Map<String, String>> infoList, 
											String[] keys, int[] cellTypes,int sheetSize) throws IOException{
		int sheetIndex = start/sheetSize+1;
		int startRow = start%sheetSize;
		/*if(sheetIndex>getSheetIndex()){
			for(int i=0;i<sheetIndex-getSheetIndex();i++){
				this.setColumnView();
				this.addSheet("订单列表");
				setTitles(titles);
			}
		}*/
		wsheet = wbook.getSheetAt(sheetIndex-1);
		if (infoList != null && !infoList.isEmpty()) {
			// 内容
			CellStyle cellStyle = getCellStyle(Cell.CELL_TYPE_STRING);
			for (;!(infoList.isEmpty());) {
				startRow++;
				Map<String, String> rowInfo = infoList.get(0);
				if (keys.length > titleCols) {
					titleCols = keys.length;
				}
				Row row = wsheet.createRow(startRow);
				row.setHeight((short) 300);
				for (int j = 0; j < keys.length; j++) {
					Cell cell = row.createCell(j);
					String value = rowInfo.get(keys[j]);
					cell.setCellValue(value == null ? "" : value);
					if (cellTypes != null) {
						cell.setCellType(cellTypes[j]);
					}
					cell.setCellStyle(cellStyle);
				}
				infoList.remove(0);
			}
		}
	}
}
